SQL STATEMENT BUILDER
With the SQL Statement Builder - surprise - you can easily build your own SQL Statements - Select, Input and Delete. It also includes Inner Joins. One of the newer features is that it's a stand-alone builder also.....pressing the 'Select' button on the bottom right side of the task bar - brings up the the SQL Builder Assistant - outputs in either String=Statement format or the basic statement by itself.

The power comes in that it will connect directly to the database of your choice - either MS Access or SQL Server (7/2000). To get help with the SQL Select statement, click on the Select button. You are presented with a window that includes three steps. If you want the easiest way through it all, choose each of the three in order. To work with Number 1, you must have a local copy of the Access database on your computer or attached network. If you have not chosen DSN-less connection, here, you Choose the database. This will automatically fill the field and table pull down boxes. Of course, if you have already chosen DSN-less connection and selected the database in that dialog, these fields will automatically be filled when the dialog loads.

If you are using an Inner Join statement, you might want to consider selecting the 'Table.Field Format' check box. If you do this, each field you choose will be in that format. This is very useful if you have tables with fields named alike.

There is also a Database Structure button. If you click here, it will pop out a 'Tree-view' of the database you have selected, with the Tables and the Fields. You can use it for merely viewing the DB structure, or double-click on the field names to populate your SQL statement.

When this pops, out, there is another button - 'TEST'. Here, you can test your SQL statements - no variables, though. Either Create your SQL Select statements with the SQL Builder Assistant, or type in your own - however, the database and tables must be chosen through the dialog box.

If you merely want to Select all, choose the table in the Table Pull-down list, click the 'Select All' check box, and click the 'From' Button.

If you want to build your statement with certain fields, then choose the Table from the Table Pull-down list. This will populate the Field Pull-down list. From this list, click on the Field you want and then click the 'Add Field' button. Repeat this action for all the fields you need. You can also double click on the individual Field Names in the Database Structure Tree to select each field. If you are going to use an Inner Join, you can change the Table Selection during this process also.
Once you are finished adding fields, make sure the Table pull-down list has the correct Table selected and click the 'From' Button.

This will give you two choices - one is to just go on. The other is to continue and make an Inner Join Statement. Once the Inner Join Button is enabled, click it. This will add the Inner Join text to the window and it also enables the 'Add Table' button. Now, select a secondary Table name from the Table Pull-down list for the Inner Join. You should know this by the Table that was already selected when you clicked the 'From' button.

Now, click the 'Add Table' button. This adds the secondary table in the statement and the word 'ON' which now enables the 'Field 1' button. Choose the first field in the statement where you want the link between tables to take place. Once this is clicked, it gives you the first field with an '=' sign after it, along with the 'Field 2' button becoming enabled. Select your secondary table and the field which matches the first field and click the 'Field 2' button.

At this point, you are finished with this section. Click the 'Enter' button.
This will enable the Third Section, where you can further narrow your search with WHERE and ORDER BY clauses.
Then, choose the items you want in the second section and press the enter key. This will make all the items in the third section available for use. Beside the word 'WHERE', there is a pull down menu with all the field names. Pick the field name for your Where clause. Then pick the operator ( =, =>, >, <=, <, <>. or LIKE). Then, if you have Request.Form or Request.Querystring variables already set on the page, you can choose the variable from the Variable pull-down menu. You will probably notice that the 'Order By' pull down box is empty...it's not, but there's not an automatic choice to make. This way, if you don't need the Order By part, you won't get it in your SQL statement. Once your 'parts' of the Select statement are assembled, click 'Apply'

If you know and understand SQL Select Statements, you will find this a breeze. If not, you may need to brush up on SQL to make this part worthwhile. Naturally, this may not be the exact form that you need for your SQL statement, but it does what it is meant to do - it gives you a great start and cuts down greatly on your typing.